﻿using DreamCMS.BLL.Factory;
using DreamCMS.Entity;
using DreamCMS.Entity.Common;
using DreamCMS.WebCore.Common;
using DreamCMS.WebCore.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DreamCMS.DAL.DataAccessObject
{
    class PostDAO : IPostDAO
    {
        #region Others
        /// <summary>
        /// （内部使用）检查“文章别名”是否重复. 当插入或更新文章的时候必须保证要么插入空Slug要么插入一个唯一的Slug
        /// </summary>
        /// <param name="post">文章实体</param>
        /// <returns>该文章的别名在数据库中是否有重复</returns>
        private void ProcessSlug(PostInfo post)
        {
            if (string.IsNullOrEmpty(post.Slug))
            {
                return;  //若别名为空，则表示没有重复。则往数据库中插入空数据。自然文章页显示时就使用ID来显示。
            }
            long i = 0;
            while (i < 2)
            {
                string cmdText = string.Empty;
                if (post.PostId == 0)  //插入文章的时候，文章ID是没有的，但插入文章页也需要判断slug是否重复
                {
                    cmdText = string.Format("select count(1) from [dm_Posts] where [slug]='{0}'  ", post.Slug);
                }
                else
                {
                    //更新文章就要看除了当前文章以外的文章有没有重复Slug
                    cmdText = string.Format("select count(1) from [dm_Posts] where [slug]='{0}'   and [postid]<>{1}", post.Slug, post.PostId);
                }
                long r = Convert.ToInt64(AccessHelper.ExecuteScalar(CommandType.Text, cmdText)); //得到结果行数
                if (r == 0) //表明没有重复行，1表示已经查询到了slug在数据库中有相应的记录
                {
                    return;  //没有重复，则退出
                }
                else if (r != 0 && i == 0)
                {
                    post.Slug += VerifyCodeHelper.GetCode(3); //如果重复，则自动更改slug. 如果第二次循环还是重复，则置空
                    i++;
                }
                else
                {
                    i++;
                }
            }
        }
        /// <summary>
        /// DataSet数据集转换为List文章列表。将数据记录依次赋予PostInfo实体的公有属性。
        /// </summary>
        /// <param name="ds">DataSet数据集</param>
        /// <returns>返回文章列表集合</returns>
        private List<PostInfo> DataSetToCommentList(DataSet ds)
        {
            List<PostInfo> list = new List<PostInfo>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                PostInfo pi = new PostInfo();
                pi.PostId = StringHelper.ObjectToInt64(dr["postid"], 0);
                pi.CategoryId = StringHelper.ObjectToInt64(dr["categoryid"], 0);
                pi.Title = Convert.ToString(dr["title"]);
                pi.Summary = Convert.ToString(dr["summary"]);
                pi.Content = Convert.ToString(dr["content"]);
                pi.Slug = Convert.ToString(dr["slug"]);
                pi.UserId = StringHelper.ObjectToInt64(dr["userid"], 0);
                pi.CommentStatus = (PostCommentStatus)StringHelper.ObjectToInt64(dr["commentstatus"], 0);
                pi.CommentCount = StringHelper.ObjectToInt64(dr["commentcount"], 0);
                pi.ViewCount = StringHelper.ObjectToInt64(dr["viewcount"], 0);

                pi.Tag = Convert.ToString(dr["tag"]);

                pi.UrlFormat = (PostUrlFormate)StringHelper.ObjectToInt64((dr["urlformat"]), 0);
                pi.Template = Convert.ToString(dr["template"]);

                pi.Recommend = (PostRecommend)StringHelper.ObjectToInt64(dr["recommend"], 0);
                pi.Status = (PostStatus)StringHelper.ObjectToInt64(dr["status"], 0);
                pi.TopStatus = (PostTopStatus)StringHelper.ObjectToInt64(dr["topstatus"], 0);
                pi.HideStatus = (PostHideStatus)StringHelper.ObjectToInt64(dr["hidestatus"], 0);
                pi.ImageUrl = dr["imageurl"].ToString();
                pi.CreateDate = Convert.ToDateTime(dr["createdate"]);
                pi.UpdateDate = Convert.ToDateTime(dr["updatedate"]);
                list.Add(pi);
            }
            return list;
        }
        #endregion

        #region Insert
        /// <summary>
        /// 添加文章
        /// </summary>
        /// <param name="postinfo">实体</param>
        /// <returns>成功返回新记录的ID,失败返回 0</returns>
        public long InsertPost(PostInfo postinfo)
        {
            ProcessSlug(postinfo); //先检查slug是否有重复，无重复再插入。
            string cmdText = @"insert into [dm_Posts]
                                (
                               [categoryid],[title],[summary],[content],[slug],[userid],[commentstatus],[commentcount],[viewcount],[tag],[urlformat],[template],[recommend],[status],[topstatus],[hidestatus],[imageurl],[createdate],[updatedate]
                                )
                                values
(@Categoryid,@Title,@Summary,@Content,@Slug,@UserId,@CommentStatus,@CommentCount,@ViewCount,@Tag,@UrlFormat,@Template,@Recommend,@Status,@TopStatus,@HideStatus,@ImageUrl,@CreateDate,@UpdateDate
                                )";
            OleDbParameter[] prams = { 	
                                AccessHelper.MakeParam("@CategoryId",OleDbType.BigInt,8,ParameterDirection.Input,postinfo.CategoryId),
								AccessHelper.MakeParam("@Title",OleDbType.VarWChar,255,ParameterDirection.Input,postinfo.Title),
								AccessHelper.MakeParam("@Summary",OleDbType.VarWChar,255,ParameterDirection.Input,postinfo.Summary),
								AccessHelper.MakeParam("@Content",OleDbType.VarWChar,0,ParameterDirection.Input,postinfo.Content),
								AccessHelper.MakeParam("@Slug",OleDbType.VarWChar,50,ParameterDirection.Input,postinfo.Slug),
								AccessHelper.MakeParam("@UserId",OleDbType.BigInt,8,ParameterDirection.Input,postinfo.UserId),
								AccessHelper.MakeParam("@CommentStatus",OleDbType.BigInt,8,ParameterDirection.Input,(long)postinfo.CommentStatus),
								AccessHelper.MakeParam("@CommentCount",OleDbType.BigInt,8,ParameterDirection.Input,postinfo.CommentCount),
								AccessHelper.MakeParam("@ViewCount",OleDbType.BigInt,8,ParameterDirection.Input,postinfo.ViewCount),
								AccessHelper.MakeParam("@Tag",OleDbType.VarWChar,255,ParameterDirection.Input,postinfo.Tag),
                                AccessHelper.MakeParam("@UrlFormat",OleDbType.BigInt,8,ParameterDirection.Input,(long)postinfo.UrlFormat),
                                AccessHelper.MakeParam("@Template",OleDbType.VarChar,50,ParameterDirection.Input,postinfo.Template ),
                                AccessHelper.MakeParam("@Recommend",OleDbType.BigInt,8,ParameterDirection.Input,(long)postinfo.Recommend),
								AccessHelper.MakeParam("@Status",OleDbType.BigInt,8,System.Data.ParameterDirection.Input,(long)postinfo.Status),
                                AccessHelper.MakeParam("@TopStatus",OleDbType.BigInt,8,ParameterDirection.Input,(long)postinfo.TopStatus),
                                AccessHelper.MakeParam("@HideStatus",OleDbType.BigInt,8,ParameterDirection.Input,(long
                                )postinfo.HideStatus),
                                AccessHelper.MakeParam("@ImageUrl",OleDbType.VarWChar,255,ParameterDirection.Input,postinfo.imageUrl),
								AccessHelper.MakeParam("@CreateDate",OleDbType.Date,0,ParameterDirection.Input,postinfo.CreateDate),
								AccessHelper.MakeParam("@UpdateDate",OleDbType.Date,0,ParameterDirection.Input,postinfo.UpdateDate)
							};
            if (AccessHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams) > 0) //只有成功查询才进行下面操作
            {
                long newId = StringHelper.ObjectToInt64(AccessHelper.ExecuteScalar(CommandType.Text, "select top 1 [postid] from [dm_Posts] order by [postid] desc"), 0);
                return newId;
            }
            return 0;
        }
        #endregion

        #region Update
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="postinfo">实体</param>
        /// <returns>修改的行数。失败返回0</returns>
        public long UpdatePost(PostInfo postinfo)
        {
            ProcessSlug(postinfo);//检查别名是否重复

            string cmdText = "update [dm_Posts] set  [categoryid]=@CategoryId,[title]=@Title,[summary]=@Summary,[content]=@Content,[slug]=@Slug,[imageurl]=@ImageUrl,[userid]=@UserId,[commentstatus]=@CommentStatus,[commentcount]=@CommentCount,[viewcount]=@ViewCount,[tag]=@Tag,[urlformat]=@UrlFormat,[template]=@Template,[recommend]=@Recommend,[status]=@Status,[topstatus]=@TopStatus,[hidestatus]=@HideStatus,[createdate]=@CreateDate,[updatedate]=@UpdateDate where [postid]=@PostId";
            OleDbParameter[] prams = { 
                               
                                AccessHelper.MakeParam("@CategoryId",OleDbType.BigInt,8,ParameterDirection.Input,postinfo.CategoryId),
								AccessHelper.MakeParam("@Title",OleDbType.VarWChar,255,ParameterDirection.Input,postinfo.Title),
								AccessHelper.MakeParam("@Summary",OleDbType.VarWChar,255,ParameterDirection.Input,postinfo.Summary),
								AccessHelper.MakeParam("@Content",OleDbType.VarWChar,0,ParameterDirection.Input,postinfo.Content),
								AccessHelper.MakeParam("@Slug",OleDbType.VarWChar,50,ParameterDirection.Input,postinfo.Slug),
                                AccessHelper.MakeParam("@ImageUrl",OleDbType.VarWChar,255,ParameterDirection.Input,postinfo.imageUrl),
								AccessHelper.MakeParam("@UserId",OleDbType.BigInt,8,ParameterDirection.Input,postinfo.UserId),
								AccessHelper.MakeParam("@CommentStatus",OleDbType.BigInt,8,ParameterDirection.Input,(long)postinfo.CommentStatus),
								AccessHelper.MakeParam("@CommentCount",OleDbType.BigInt,8,ParameterDirection.Input,postinfo.CommentCount),
								AccessHelper.MakeParam("@ViewCount",OleDbType.BigInt,8,ParameterDirection.Input,postinfo.ViewCount),
								AccessHelper.MakeParam("@Tag",OleDbType.VarWChar,255,ParameterDirection.Input,postinfo.Tag),
                                AccessHelper.MakeParam("@UrlFormat",OleDbType.BigInt,8,ParameterDirection.Input,(long)postinfo.UrlFormat),
                                AccessHelper.MakeParam("@Template",OleDbType.VarChar,50,ParameterDirection.Input,postinfo.Template ),
                                AccessHelper.MakeParam("@Recommend",OleDbType.BigInt,8,ParameterDirection.Input,(long)postinfo.Recommend),
								AccessHelper.MakeParam("@Status",OleDbType.BigInt,8,ParameterDirection.Input,(long)postinfo.Status),
                                AccessHelper.MakeParam("@TopStatus",OleDbType.BigInt,8,ParameterDirection.Input,(long)postinfo.TopStatus),
                                AccessHelper.MakeParam("@HideStatus",OleDbType.BigInt,8,ParameterDirection.Input,(long)postinfo.HideStatus),
								AccessHelper.MakeParam("@CreateDate",OleDbType.Date,8,ParameterDirection.Input,postinfo.CreateDate),
								AccessHelper.MakeParam("@UpdateDate",OleDbType.Date,8,ParameterDirection.Input,postinfo.UpdateDate),
                                AccessHelper.MakeParam("@PostId",OleDbType.BigInt,8,ParameterDirection.Input,postinfo.PostId)
							};
            long rel = AccessHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
            if (rel > 0)
            {
                return rel;
            }
            return 0;
        }

        /// <summary>
        /// 更新文章访问数
        /// </summary>
        /// <param name="postId">文章ID</param>
        /// <param name="addCount">增加的访问量</param>
        /// <returns>更新的记录数，没有文章或失败返回0</returns>
        public long UpdatePostViewCount(long postId, long addCount)
        {
            string cmdText = "update [dm_Posts] set [viewcount] = [viewcount] + @addcount where [postid]=@postid";
            OleDbParameter[] prams = { 
								AccessHelper.MakeParam("@addcount",OleDbType.Integer,8,ParameterDirection.Input,addCount),
                                AccessHelper.MakeParam("@postid",OleDbType.Integer,8,ParameterDirection.Input,postId),
							};
            return AccessHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);

        }
        #endregion

        #region Delete
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="PostId">主键</param>
        /// <returns>删除的行数</returns>
        public long DeletePost(long postid)
        {
            string cmdText = "delete from [dm_Posts] where [postid] = @PostId";
            OleDbParameter[] prams = { 
								AccessHelper.MakeParam("@PostId",OleDbType.BigInt,8,ParameterDirection.Input,postid)
							};
            long result = AccessHelper.ExecuteNonQuery(CommandType.Text, cmdText, prams);
            if (result > 0)
            {
                return result;
            }
            return 0;
        }
        /// <summary>
        /// 根据类别删除该类别写的文章
        /// </summary>
        /// <param name="catId">类别ID</param>
        /// <returns>删除的行数</returns>
        public long DeletePostByCat(long catId)
        {
            string cmdText = "delete from [dm_Posts] where [categoryid]=@catId";
            OleDbParameter[] paras = new OleDbParameter[] { AccessHelper.MakeParam("catId", OleDbType.BigInt, 8, ParameterDirection.Input, catId) };
            long result = AccessHelper.ExecuteNonQuery(CommandType.Text, cmdText, paras);
            return result;
        }
        #endregion

        #region Get
        /// <summary>
        /// 根据文章ID获取文章实体
        /// </summary>
        /// <param name="PostId">文章ID（主键）</param>
        /// <returns>主键为传入ID的首条记录（有且只有一条）</returns>
        public PostInfo GetPost(long postid)
        {
            string cmdText = "select top 1 * from [dm_Posts] where [postid] = @PostId";
            OleDbParameter[] prams = { 
								        AccessHelper.MakeParam("@PostId",OleDbType.BigInt,8,ParameterDirection.Input,postid)
							            };


            List<PostInfo> list = DataSetToCommentList(AccessHelper.ExecuteDataSet(CommandType.Text, cmdText, prams));

            return list.Count > 0 ? list[0] : null;
        }

        /// <summary>
        /// 根据别名来获取实体
        /// </summary>
        /// <param name="slug">别名（文章的主键）</param>
        /// <returns>返回根据别名检索到的文章实体</returns>
        public PostInfo GetPost(string slug)
        {
            string cmdText = "select top 1 * from [dm_Posts] where [slug] = @slug";
            OleDbParameter[] prams = { 
								        AccessHelper.MakeParam("@slug",OleDbType.VarWChar,50,ParameterDirection.Input,slug)
							            };
            List<PostInfo> list = DataSetToCommentList(AccessHelper.ExecuteDataSet(CommandType.Text, cmdText, prams));
            return list.Count > 0 ? list[0] : null;
        }

        /// <summary>
        /// 获取所有文章列表（降序，ID由新到旧，时间由新到旧）
        /// </summary>
        /// <returns>IList</returns>
        public List<PostInfo> GetPostList()
        {
            string cmdText = "select * from [dm_Posts] order by [postid] desc"; //由于ID是自增的，因此ID倒序相当于时间由新到旧
            return DataSetToCommentList(AccessHelper.ExecuteDataSet(CommandType.Text, cmdText));
        }

        /// <summary>
        /// 获取分页文章
        /// </summary>
        /// <param name="pageSize">页容量</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="recordCount">文章总记录数</param>
        /// <param name="categoryId">类别ID</param>
        /// <param name="tagId">标签ID</param>
        /// <param name="userId">用户ID</param>
        /// <param name="recommend">是否推荐</param>
        /// <param name="status">状态</param>
        /// <param name="topstatus">是否置顶</param>
        /// <param name="hidestatus">是否隐藏</param>
        /// <param name="begindate">开始时间</param>
        /// <param name="enddate">结束时间</param>
        /// <param name="keyword">搜索关键词</param>
        /// <returns>分页文章实体的集合</returns>
        public List<PostInfo> GetPostList(long pageSize, long pageIndex, out long recordCount, long categoryId, long tagId, long userId, long recommend, long status, long topstatus, long hidestatus, string begindate, string enddate, string keyword)
        {
            string condition = " 1=1 ";

            if (categoryId != -1)
            {
                condition += " and categoryId=" + categoryId;
            }
            if (tagId != -1)
            {
                condition += " and tag like '%{" + tagId + "}%'";
            }
            if (userId != -1)
            {
                condition += " and userid=" + userId;
            }
            if (recommend != -1)
            {
                condition += " and recommend=" + recommend;
            }
            if (status != -1)
            {
                condition += " and status=" + status;
            }

            if (topstatus != -1)
            {
                condition += " and topstatus=" + topstatus;
            }

            if (hidestatus != -1)
            {
                condition += " and hidestatus=" + hidestatus;
            }

            if (!string.IsNullOrEmpty(begindate))
            {
                condition += " and createdate>=#" + begindate + "#";
            }
            if (!string.IsNullOrEmpty(enddate))
            {
                condition += " and createdate<#" + enddate + "#";
            }

            if (!string.IsNullOrEmpty(keyword))
            {
                condition += string.Format(" and (summary like '%{0}%' or title like '%{0}%'  )", keyword);
            }

            string cmdTotalRecord = "select count(1) from [dm_Posts] where " + condition;
            //获取此次查询的结果记录数（没有或者转换失败则为0）
            recordCount = StringHelper.ObjectToInt64(AccessHelper.ExecuteScalar(CommandType.Text, cmdTotalRecord), 0);
            //获取分页的SQL语句，用于获取相应页码的记录，而不是所有记录。
            string cmdText = AccessHelper.GetPageSql("[dm_Posts]", "[postid]", "*", pageSize, pageIndex, 1, condition);

            return DataSetToCommentList(AccessHelper.ExecuteDataSet(CommandType.Text, cmdText));
        }
        /// <summary>
        /// 获取相关文章
        /// </summary>
        /// <param name="postId">文章ID</param>
        /// <param name="rowCount">要获取的文章条数</param>
        /// <returns>相关文章的集合</returns>
        public List<PostInfo> GetPostListByRelated(long postId, long rowCount)
        {
            string tags = string.Empty;

            PostInfo post = GetPost(postId);

            if (post != null && post.Tag.Length > 0)
            {
                tags = post.Tag;

                tags = tags.Replace("}", "},");
                string[] idList = tags.Split(',');

                string where = " (";
                foreach (string tagID in idList)
                {
                    if (!string.IsNullOrEmpty(tagID))
                    {
                        where += string.Format("  [tags] like '%{0}%' or ", tagID);
                    }
                }
                where += " 1=2 ) and [status]=1 and [postid]<>" + postId;

                string cmdText = string.Format("select top {0} * from [dm_Posts] where {1} order by [postid] desc", rowCount, where);

                return DataSetToCommentList(AccessHelper.ExecuteDataSet(CommandType.Text, cmdText));
            }
            return new List<PostInfo>();
        }
        #endregion
    }
}
